﻿using System;
using System.Drawing;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Practices.Unity;
using Excel = Microsoft.Office.Interop.Excel;
using Infrastucture;
using DashboardModule.ViewModels;
using TimeSplicerModule.DomainModels;
using TimeSplicerModule.Services;
using TimeSplicerModule.Security;
using TimeSplicerModule.Factories;

namespace DashboardModule.Services.Temp
{
    // TODO: Need to create interface
    public interface ITempReportsService
    {
        TempReportsViewModel GetViewModel();
        void WeeklyActivityReport(UserModel user, DateTime startDate, DateTime endDate);
        void ProjectSummary(UserModel user);
        void ProjectDetails(UserModel user, ProjectModel project);
    }

    /// <summary>
    /// This is a temporary service to provide some reporting.
    /// </summary>
    public class TempReportsService : ITempReportsService
    {
        // May need to add option to pull all project tasks if PM of that project


    

        #region Serivices...
        private readonly IUnityContainer _container;
        private ISession _session;
        private readonly IUserService _userService;
        private readonly IProjectService _projectService;
        private readonly IProjectFactory _projectFactory;
        private readonly ITaskService _taskService;
        private readonly ITaskFactory _taskFactory;
        private readonly IStatusFactory _statusFactory;
        private readonly ICategoryFactory _categoryFactory;
        private readonly IProjectUserFactory _projectUserFactory;
        #endregion

        #region Constructors...
        public TempReportsService(IUnityContainer container, IUserService userService, IProjectService projectService, IProjectFactory projectFactory, 
            ITaskService taskService, ITaskFactory taskFactory, IStatusFactory statusFactory, ICategoryFactory categoryFactory, IProjectUserFactory projectUserFactory)
        {
            _container = container;
            _userService = userService;
            _projectService = projectService;
            _projectFactory = projectFactory;
            _taskService = taskService;
            _taskFactory = taskFactory;
            _statusFactory = statusFactory;
            _categoryFactory = categoryFactory;
            _projectUserFactory = projectUserFactory;
        }
        #endregion


        // get view moodel
        public TempReportsViewModel GetViewModel()
        {
            _session = _container.Resolve<ISession>();

            var model = new TempReportsViewModel();
            model.Users = _userService.GetViewableUsers();
            //model.Projets = _projectFactory.GetList() // think i need to get by user list
            
            //model.SelectedUserGuid = _session.User.UserGuid;
            //model.Range = new daterange(DateTime.Today.AddDays(-7), DateTime.Today);
            model.StartDate = DateTime.Today.AddDays(-7);
            model.EndDate = DateTime.Today;
            model.Projects = _projectService.GetList(model.Users);

            if (model.Projects != null && model.Projects.Count > 0)
                model.SelectedProjectGuid = model.Projects.FirstOrDefault().ProjectGuid;

            model.ProjectUsers = new List<ProjectUserModel>();
            
            foreach (var u in model.Users)
            {
                var projUser = _projectUserFactory.GetList(x => x.UserGuid == u.UserGuid);
                model.ProjectUsers.AddRange(projUser);
            }

            // set this here to trigger project filter
            model.SelectedUserGuid = _session.User.UserGuid;


            return model;
        }

        #region By User...
        public void WeeklyActivityReport(UserModel user, DateTime startDate, DateTime endDate)
        {
            // get statuses
            var statuses = _statusFactory.GetList();
            var categoryies = _categoryFactory.GetList();
            var projects = _projectService.GetList(user);
            WeeklySummaryReport model = new WeeklySummaryReport();
            model.Projects = new List<ProjectReportModel>();
            List<Guid> projectIds = new List<Guid>();

            // get tasks by range worked on
            daterange range = new daterange(startDate, endDate);
            var tasks = _taskService.GetTaskListByWorkedOnDateRange(user, range);
            List<TaskReportModel> reportTasks = new List<TaskReportModel>();

            // get projects for each task
            foreach (var t in tasks)
            {
                t.Status = statuses.FirstOrDefault(x => x.StatusGuid == t.StatusGuid);
                t.Project = projects.FirstOrDefault(x => x.ProjectGuid == t.ProjectGuid);
                t.Project.Status = statuses.FirstOrDefault(x => x.StatusGuid == t.StatusGuid);
                projectIds.Add(t.Project.ProjectGuid);
            }

            // convert to weekly model
            var distinctIds = projectIds.Distinct().ToList();

            int taskCount = 0;
            double totalHours = 0D;
            foreach (var i in distinctIds)
            {
                var project = projects.FirstOrDefault(x => x.ProjectGuid == i);
                project.Tasks = tasks.Where(x => x.ProjectGuid == i).ToList();

                double projectHours = 0D;
                foreach (var t in project.Tasks)
                {
                    double taskHours = 0D;
                    // get hours for the date range
                    var stamps = t.Timestamps.Where(x => x.StartTime >= range.StartDate && x.EndTime <= range.EndDate).ToList();
                    taskHours += t.ActualHours; //_taskService.GetTaskHours(stamps);
                    projectHours += taskHours;

                    TaskReportModel r = new TaskReportModel
                    {
                        CategoryGuid = t.CategoryGuid,
                        SubCategoryGuid = t.SubCategoryGuid,
                        Category = categoryies.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid),
                        SubCategory = categoryies.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid),
                        Title = t.Name,
                        Description = t.Description,
                        TaskHours = taskHours
                    };

                    reportTasks.Add(r);
                }

                ProjectReportModel m = new ProjectReportModel
                {
                    ProjectName = project.Name,
                    TaskCount = project.Tasks.Count,
                    Category = categoryies.FirstOrDefault(x => x.CategoryGuid == project.CategoryGuid),
                    Description = project.Description,
                    ProjectHours = projectHours
                };

                model.Projects.Add(m);

                totalHours += projectHours;
                taskCount += project.Tasks.Count;
            }

            model.ProjectCount = model.Projects.Count;
            model.TaskCount = taskCount;
            model.TotalHours = totalHours;
            model.Tasks = reportTasks;
         
           

            Excel.Application excel = new Excel.Application();
            Excel.Workbook book = excel.Workbooks.Add();
            Excel.Worksheet sheet = book.Worksheets[1];

            //excel.Visible = true;


            createHeader(sheet, String.Format("Week Beginning {0:MM/dd/yyyy} and Ending {1:MM/dd/yyyy}", startDate, endDate));
            formatWeeklyPage(sheet, model); // TODO: Will need to update this method


            excel.ActiveWindow.DisplayGridlines = false;
            excel.Visible = true;
            sheet = null;
            book = null;
            excel = null;
        }
        #endregion

        public void ProjectSummary(UserModel user)
        {

            var statuses = _statusFactory.GetList();
            var categoryies = _categoryFactory.GetList();
            var projects = _projectService.GetList(user);
            var tasks = _taskService.GetTasks(user);
            var projectModels = new List<ProjectReportModel>();


            int taskCount = 0;
            double totalHours = 0D;
            foreach (var p in projects)
            {
                var projectTasks = tasks.Where(x => x.ProjectGuid == p.ProjectGuid).ToList();
                var tks = new List<TaskReportModel>();

                double projectHours = 0D;
                foreach (var t in projectTasks)
                {
                    double taskHours = 0D;
                    // get hours for the date range
                    //var stamps = t.Timestamps.Where(x => x.StartTime >= range.StartDate && x.EndTime <= range.EndDate).ToList();
                    taskHours += t.ActualHours; //_taskService.GetTaskHours(t.Timestamps);
                    projectHours += taskHours;

                    var r = new TaskReportModel
                    {
                        Title = t.Name,
                        Description = t.Description,
                        CategoryGuid = t.CategoryGuid,
                        SubCategoryGuid = t.SubCategoryGuid,
                        Category = categoryies.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid),
                        SubCategory = categoryies.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid),
                        Status = statuses.FirstOrDefault(x => x.StatusGuid == t.StatusGuid),
                        TaskHours = taskHours
                    };

                    tks.Add(r);
                }

                var m = new ProjectReportModel
                {
                    Category = categoryies.FirstOrDefault(x => x.CategoryGuid == p.CategoryGuid),
                    Status = statuses.FirstOrDefault(x => x.StatusGuid == p.StatusGuid),
                    ProjectName = p.Name,
                    Description = p.Description,
                    Tasks = tks,
                    TaskCount = tks.Count,
                    ProjectHours = projectHours
                };

               

                projectModels.Add(m);
            }
            

            Excel.Application excel = new Excel.Application();
            Excel.Workbook book = excel.Workbooks.Add();
            Excel.Worksheet sheet = book.Worksheets[1];

            //excel.Visible = true;

            createHeader(sheet, "Projects Summary");
            formatProjectSummary(sheet, projectModels);

            excel.ActiveWindow.DisplayGridlines = false;
            excel.Visible = true;
        }

        public void ProjectDetails(UserModel user, ProjectModel project)
        {
            //ProjectStatisticsService _projectStatsService = new ProjectStatisticsService();

            //var response = await _projectStatService.GetListAsync(user).ConfigureAwait(false);
            //var model = response.Models.FirstOrDefault(x => x.ProjectGuid == project.ProjectGuid);

            var statuses = _statusFactory.GetList();
            var categoryies = _categoryFactory.GetList();
            project.Tasks = _taskService.GetTasks(project, user);


            List<TaskReportModel> tasks = new List<TaskReportModel>();

            double projectHours = 0D;
            foreach (var t in project.Tasks)
            {
                double taskHours = 0D;
                // get hours for the date range
                //var stamps = t.Timestamps.Where(x => x.StartTime >= range.StartDate && x.EndTime <= range.EndDate).ToList();
                taskHours += t.ActualHours; //_taskService.GetTaskHours(t.Timestamps);
                projectHours += taskHours;

                var r = new TaskReportModel
                {
                    Title = t.Name,
                    Description = t.Description,
                    CategoryGuid = t.CategoryGuid,
                    SubCategoryGuid = t.SubCategoryGuid,
                    Category = categoryies.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid),
                    SubCategory = categoryies.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid),
                    Status = statuses.FirstOrDefault(x => x.StatusGuid == t.StatusGuid),
                    TaskHours = taskHours
                };

                tasks.Add(r);
            }

            ProjectReportModel model = new ProjectReportModel
            {
                Category = categoryies.FirstOrDefault(x => x.CategoryGuid == project.CategoryGuid),
                Status = statuses.FirstOrDefault(x => x.StatusGuid == project.StatusGuid),
                ProjectName = project.Name,
                Description = project.Description,
                Tasks = tasks,
                TaskCount = tasks.Count,
                ProjectHours = projectHours
            };

            

            Excel.Application excel = new Excel.Application();
            Excel.Workbook book = excel.Workbooks.Add();
            Excel.Worksheet sheet = book.Worksheets[1];

            //excel.Visible = true;


            formatProjectPage(sheet, model);


            excel.ActiveWindow.DisplayGridlines = false;
            excel.Visible = true;
        }

        private void createHeader(Excel.Worksheet sheet, string headerText)
        {
            sheet.Range["A1:X3"].MergeCells = true;

            Excel.Range header = sheet.Range["A1"];

            header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            header.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            header.Font.Name = "Verdana";
            header.Font.Bold = true;
            header.Font.Size = 20;
            header.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(64, 73, 83));
            header.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));


            header.Value2 = headerText;
        }

        private void formatProjectSummary(Excel.Worksheet sheet, IList<ProjectReportModel> projects)
        {
            ProjectSummaryReportModel summaryModel = new ProjectSummaryReportModel
            {
                ProjectCount = projects.Count,
                TaskCount = projects.Sum(x => x.Tasks.Count),
                TotalHours = projects.Sum(x => x.ProjectHours)
            };
            // need project summary
            // three columns for now
            // left project count, taks count, total hours 
            Excel.Range totalsRange = sheet.Range["B5:C8"];

            totalsRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0, 176, 240));
            totalsRange.Font.Name = "Verdana";
            totalsRange.Font.Size = 11;
            totalsRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            sheet.Range["B5:B8"].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            sheet.Range["C5:C8"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["C5:C8"].Font.Bold = true;

            sheet.Range["B2"].ColumnWidth = 27;
            sheet.Range["C2"].ColumnWidth = 20;

            // values
            sheet.Range["B5"].Value2 = "Number of Projects:";
            sheet.Range["B6"].Value2 = "Total Project Hours:";
            sheet.Range["B7"].Value2 = "Number of Tasks:";
            sheet.Range["B8"].Value2 = "Average Time Per Task:";

            // int projectCount = _projectStatService.GetProjectCount().Model;
            // double projectHours = _projectStatService.GetProjectHours().Model;
            //int taskCount = _taskStatService.GetTaskCount().Model;
            //double avgTaskTime = projectHours / taskCount;

            sheet.Range["C5"].Value2 = summaryModel.ProjectCount;
            sheet.Range["C6"].Value2 = String.Format("{0:0.00}", summaryModel.TotalHours);
            sheet.Range["C7"].Value2 = summaryModel.TaskCount;
            sheet.Range["C8"].Value2 = String.Format("{0:0.00}", summaryModel.AverageTaskTime);

            // description section


            // project list - changing to pull the project report model
            //IList<ProjectReportModel> projects = _projectStatService.GetList().Models;
            projects = projects.OrderByDescending(x => x.ProjectHours).ToList(); // order by hours
            //IList<ProjectViewModel> projects = _projectService.GetList().Models;
            int row = 11;
            int lastRow = 10 + projects.Count;
            Excel.Range chartRange = sheet.get_Range("B10", "C" + lastRow);

            // formatting
            Excel.Range projectRange = sheet.get_Range("B11", "C" + lastRow);
            projectRange.Font.Name = "Verdana";
            projectRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
            projectRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(112, 173, 71));
            sheet.Range["C11" + ":C" + lastRow].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            // add headers
            sheet.Range["B10"].Value2 = "Project";
            sheet.Range["C10"].Value2 = "Hours";
            Excel.Range projectHeaderRange = sheet.Range["B10:C10"];
            projectHeaderRange.Font.Name = "Verdana";
            projectHeaderRange.Font.Bold = true;
            projectHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            projectHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(198, 224, 180));

            foreach (var p in projects)
            {
                // should get hours first then order them
                // 

                sheet.Range["B" + row].Value2 = p.ProjectName;
                //double hours = p.ProjectHours; //_projectStatService.GetProjectHours(p.ProjectGuid).Model;
                sheet.Range["C" + row].Value2 = String.Format("{0:0.00}", p.ProjectHours);

                ++row;
            }

            // pie chart
            Excel.ChartObjects chartObjects = sheet.ChartObjects(Type.Missing);
            Excel.Chart projectTimeChart = chartObjects.Add(325, 60, 500, 300).Chart;

            // format chart
            projectTimeChart.SetSourceData(chartRange, Type.Missing);
            projectTimeChart.ChartType = Excel.XlChartType.xlDoughnut;
            projectTimeChart.HasTitle = true;
            projectTimeChart.ChartTitle.Text = "Hours";
            projectTimeChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent);
            // projectTimeChart.ChartStyle = 253; //should probaby not use this in case 2010 does not have this chart style

        }

        private void formatProjectPage(Excel.Worksheet sheet, ProjectReportModel project)
        {
            // todo: need to fix project name to remove special characters
            string sheetName = project.ProjectName.Replace("/", " ");

            try
            {
                if (sheetName.Length > 15)
                    sheet.Name = sheetName.Substring(0, 15);
                else
                    sheet.Name = sheetName;
            }
            catch (Exception ex)
            {
                sheet.Name = "err";
            }

            createHeader(sheet, project.ProjectName);

            // format summary areas
            Excel.Range totalsRange = sheet.Range["B5:C8"];

            totalsRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0, 176, 240));
            totalsRange.Font.Name = "Verdana";
            totalsRange.Font.Size = 11;
            totalsRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            sheet.Range["B5:B8"].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            sheet.Range["C5:C8"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["C5:C8"].Font.Bold = true;

            sheet.Range["B2"].ColumnWidth = 27;
            sheet.Range["C2"].ColumnWidth = 35;
            sheet.Range["I2"].ColumnWidth = 11;

            // values
            sheet.Range["B5"].Value2 = "Status:";
            sheet.Range["B6"].Value2 = "Project Hours:";
            sheet.Range["B7"].Value2 = "Number of Tasks:";
            sheet.Range["B8"].Value2 = "Average Time Per Task:";

            sheet.Range["C5"].Value2 = project.Status.Name;
            sheet.Range["C6"].Value2 = String.Format("{0:0.00}", project.ProjectHours);
            sheet.Range["C7"].Value2 = project.TaskCount;
            sheet.Range["C8"].Value2 = String.Format("{0:0.00}", project.AverageTaskTime);

            // description
            sheet.Range["D5"].ColumnWidth = 3;
            sheet.Range["E5"].ColumnWidth = 14;
            Excel.Range descRange = sheet.get_Range("E5", "I8");
            descRange.Font.Name = "Verdana";
            descRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
            descRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0, 176, 240));
            sheet.Range["E5:E8"].MergeCells = true;
            sheet.Range["E5"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["E5"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            sheet.Range["E5"].WrapText = true;
            sheet.Range["F5:I8"].MergeCells = true;
            sheet.Range["F5"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["F5"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            sheet.Range["F5"].WrapText = true;

            sheet.Range["E5"].Value2 = "Project Description:";
            sheet.Range["F5"].Value2 = project.Description;



            // set start row
            int row = 11;
            int lastRow = 10; //+ project.Branches

            // branch list
            //Excel.Range range = sheet.get_Range("B10", "I" + lastRow);
            //Excel.Range dataRange = sheet.get_Range("K10", "L" + lastRow);
            //dataRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            //// formatting
            //range.Font.Name = "Verdana";
            //sheet.Range["D10:H10"].MergeCells = true;

            //// headers
            //sheet.Range["B10"].Value2 = "Project Branch";
            //sheet.Range["C10"].Value2 = "Category";
            //sheet.Range["D10"].Value2 = "Description";
            //sheet.Range["I10"].Value2 = "Hours";

            // hcart headers
            //sheet.Range["K10"].Value2 = "Branch";
            //sheet.Range["L10"].Value2 = "Hours";


            // headers
            //Excel.Range branchHeaderRange = sheet.Range["B10:I10"];
            //branchHeaderRange.Font.Bold = true;
            //branchHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //branchHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(198, 224, 180));


            // pie chart
            Excel.ChartObjects chartObjects = sheet.ChartObjects(Type.Missing);
            //Excel.Chart branchChart = chartObjects.Add(700, 75, 500, 300).Chart;

            // format chart
            //branchChart.SetSourceData(dataRange, Type.Missing);
            //branchChart.ChartType = Excel.XlChartType.xlDoughnut;
            //branchChart.HasTitle = true;
            //branchChart.ChartTitle.Text = "Branch Hours";
            //branchChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent);
            // projectTimeChart.ChartStyle = 253; //should probaby not use this in case 2010 does not have this chart style


            // tasks are next
            List<TaskReportModel> tasks = new List<TaskReportModel>();

            project.Tasks.ForEach(x => { tasks.Add(x); });

            ++row;
            lastRow = row + tasks.Count;

            // task list
            Excel.Range range = sheet.get_Range("B" + row, "I" + lastRow);
            Excel.Range taskRange = sheet.get_Range("K" + row, "L" + lastRow);
            taskRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));


            // formatting
            range.Font.Name = "Verdana";
            sheet.Range["D" + row + ":H" + row].MergeCells = true;



            // headers
            sheet.Range["B" + row].Value2 = "Task";
            sheet.Range["C" + row].Value2 = "Category/Sub Category";
            sheet.Range["D" + row].Value2 = "Description";
            sheet.Range["I" + row].Value2 = "Hours";

            // hcart headers
            sheet.Range["K" + row].Value2 = "Task";
            sheet.Range["L" + row].Value2 = "Hours";

            // reorder tasks
            tasks = tasks.OrderByDescending(x => x.TaskHours).ToList();

            // headers
            Excel.Range taskHeaderRange = sheet.Range["B" + row + ":I" + row];
            taskHeaderRange.Font.Bold = true;
            taskHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            taskHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(142, 169, 219));



            // eneter records
            ++row;
            foreach (var t in tasks)
            {

                // formatting
                Excel.Range r = sheet.Range["B" + row + ":I" + row];
                sheet.Range["D" + row + ":H" + row].MergeCells = true;
                r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(68, 114, 196));
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["I" + row].NumberFormat = "0.00";


                sheet.Range["B" + row].Value2 = t.Title;
                sheet.Range["C" + row].Value2 = String.Format("{0}/{1}", t.Category.Name, t.SubCategory.Name);
                sheet.Range["D" + row].Value2 = t.Description;
                sheet.Range["I" + row].Value2 = String.Format("{0:0.00}", t.TaskHours);
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


                // chart data area
                sheet.Range["K" + row].Value2 = t.Title;
                sheet.Range["L" + row].Value2 = t.TaskHours;

                ++row;
            }

            // pie chart
            Excel.Chart taskChart = chartObjects.Add(700, 75, 500, 300).Chart;

            // format chart
            taskChart.SetSourceData(taskRange, Type.Missing);
            taskChart.ChartType = Excel.XlChartType.xlDoughnut;
            taskChart.HasTitle = true;
            taskChart.ChartTitle.Text = "Task Hours";
            taskChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent);
        }

        private void formatWeeklyPage(Excel.Worksheet sheet, WeeklySummaryReport summary)
        {
            List<CategoryModel> categories = new List<CategoryModel>();
            

            Excel.Range totalsRange = sheet.Range["B5:C8"];
            
            totalsRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0, 176, 240));
            totalsRange.Font.Name = "Verdana";
            totalsRange.Font.Size = 11;
            totalsRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            sheet.Range["B5:B8"].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            sheet.Range["C5:C8"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["C5:C8"].Font.Bold = true;

            sheet.Range["A1"].ColumnWidth = 2;
            sheet.Range["B2"].ColumnWidth = 27;
            sheet.Range["C2"].ColumnWidth = 32;

            // values
            sheet.Range["B5"].Value2 = "Number of Projects:";
            sheet.Range["B6"].Value2 = "Total Project Hours:";
            sheet.Range["B7"].Value2 = "Number of Tasks:";
            sheet.Range["B8"].Value2 = "Average Time Per Task:";

     
            sheet.Range["C5"].Value2 = summary.ProjectCount;
            sheet.Range["C6"].Value2 = summary.TotalHours;
            sheet.Range["C7"].Value2 = summary.TaskCount;
            sheet.Range["C8"].Value2 = String.Format("{0:0.00}", summary.AverageTaskTime);


            #region Project List...
            // set start row
            int row = 11;
            int lastRow = 10 + summary.Projects.Count;

            // branch list
            Excel.Range range = sheet.get_Range("B10", "J" + lastRow);
            Excel.Range dataRange = sheet.get_Range("K10", "L" + lastRow);
            dataRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            // formatting
            range.Font.Name = "Verdana";
            sheet.Range["D10:H10"].MergeCells = true;

            // headers
            sheet.Range["B10"].Value2 = "Project";
            sheet.Range["C10"].Value2 = "Category";
            sheet.Range["D10"].Value2 = "Description";
            sheet.Range["I10"].Value2 = "Hours";
            sheet.Range["J10"].Value2 = "%";
            sheet.Range["J10"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


            // hcart headers
            sheet.Range["K10"].Value2 = "Branch";
            sheet.Range["L10"].Value2 = "Hours";

            // reorder branches
            summary.Projects = summary.Projects.OrderByDescending(x => x.ProjectHours).ToList();

            // headers
            Excel.Range branchHeaderRange = sheet.Range["B10:J10"];
            branchHeaderRange.Font.Bold = true;
            branchHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            branchHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(198, 224, 180));

            // eneter records
            foreach (var p in summary.Projects)
            {

                // formatting
                Excel.Range r = sheet.Range["B" + row + ":J" + row];
                sheet.Range["D" + row + ":H" + row].MergeCells = true;
                r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(112, 173, 71));
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["I" + row].NumberFormat = "0.00";

                // now here
                sheet.Range["B" + row].Value2 = p.ProjectName;
                sheet.Range["C" + row].Value2 = p.Category.Name;
                sheet.Range["D" + row].Value2 = p.Description;
                sheet.Range["I" + row].Value2 = p.ProjectHours;
                sheet.Range["J" + row].Value2 = p.ProjectHours / summary.TotalHours;
                sheet.Range["J" + row].NumberFormat = "00%";
                sheet.Range["J" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


                // chart data area
                sheet.Range["K" + row].Value2 = p.ProjectName;
                sheet.Range["L" + row].Value2 = p.ProjectHours;

                ++row;
            }

            // pie chart
            Excel.ChartObjects chartObjects = sheet.ChartObjects(Type.Missing);
            Excel.Chart branchChart = chartObjects.Add(700, 75, 500, 300).Chart;

            // format chart
            branchChart.SetSourceData(dataRange, Type.Missing);
            branchChart.ChartType = Excel.XlChartType.xlDoughnut;
            branchChart.HasTitle = true;
            branchChart.ChartTitle.Text = "Project Hours";
            branchChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent);
            // projectTimeChart.ChartStyle = 253; //should probaby not use this in case 2010 does not have this chart style
            #endregion

            #region Task List...
            ++row;
            lastRow = row + summary.Tasks.Count;

            // task list
            range = sheet.get_Range("B" + row, "J" + lastRow);
            Excel.Range taskRange = sheet.get_Range("K" + row, "L" + lastRow);
            taskRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));


            // formatting
            range.Font.Name = "Verdana";
            sheet.Range["D" + row + ":H" + row].MergeCells = true;



            // headers
            sheet.Range["B" + row].Value2 = "Task";
            sheet.Range["C" + row].Value2 = "Category/Sub Category";
            sheet.Range["D" + row].Value2 = "Description";
            sheet.Range["I" + row].Value2 = "Hours";
            sheet.Range["J" + row].Value2 = "%";
            sheet.Range["J" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            // hcart headers
            sheet.Range["K" + row].Value2 = "Task";
            sheet.Range["L" + row].Value2 = "Hours";

            // reorder tasks
            summary.Tasks = summary.Tasks.OrderByDescending(x => x.TaskHours).ToList();

            // headers
            Excel.Range taskHeaderRange = sheet.Range["B" + row + ":J" + row];
            taskHeaderRange.Font.Bold = true;
            taskHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            taskHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(142, 169, 219));


            // eneter records
            ++row;
            foreach (var t in summary.Tasks)
            {

                // formatting
                Excel.Range r = sheet.Range["B" + row + ":J" + row];
                sheet.Range["D" + row + ":H" + row].MergeCells = true;
                r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(68, 114, 196));
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["I" + row].NumberFormat = "0.00";


                sheet.Range["B" + row].Value2 = t.Title;
                sheet.Range["C" + row].Value2 = String.Format("{0}/{1}", t.Category.Name, t.SubCategory.Name);
                sheet.Range["D" + row].Value2 = t.Description;
                sheet.Range["I" + row].Value2 = String.Format("{0:0.00}", t.TaskHours);
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["J" + row].Value2 = t.TaskHours / summary.TotalHours;
                sheet.Range["J" + row].NumberFormat = "00%";
                sheet.Range["J" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


                // chart data area
                sheet.Range["K" + row].Value2 = t.Title;
                sheet.Range["L" + row].Value2 = t.TaskHours;

                // capture categories
                categories.Add(t.Category);

                ++row;
            }

            // pie chart
            Excel.Chart taskChart = chartObjects.Add(700, 400, 500, 300).Chart;

            // format chart
            taskChart.SetSourceData(taskRange, Type.Missing);
            taskChart.ChartType = Excel.XlChartType.xlDoughnut;
            taskChart.HasTitle = true;
            taskChart.ChartTitle.Text = "Task Hours";
            taskChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent);
            #endregion

            #region Task Category Breakdown...
            // get a distinct list of the task category guids
            // then group and 

            var categoryGuids = categories.Select(x => new { CategoryGuid = x.CategoryGuid }).Distinct().ToList();

            List<CategoryHours> categoryStats = new List<CategoryHours>();

            foreach (var g in categoryGuids)
            {
                CategoryModel category = categories.FirstOrDefault(c => c.CategoryGuid == g.CategoryGuid) ?? new CategoryModel();
                double hours = summary.Tasks.Where(t => t.CategoryGuid == g.CategoryGuid).Sum(s => s.TaskHours);

                CategoryHours cat = new CategoryHours { Name = category.Name, Hours = hours };

                categoryStats.Add(cat);
            }

            //categoryGuids.ForEach(x =>
            //{
            //    CategoryModel category = categories.FirstOrDefault(c => c.CategoryGuid == x.CategoryGuid) ?? new CategoryModel();
            //    double hours = summary.Tasks.Where(t => t.CategoryGuid == x.CategoryGuid).Sum(s => s.TaskHours);

            //    CategoryHours cat = new CategoryHours { Name = category.Name, Hours = hours };

            //    categoryStats.Add(cat);
            //});


            ++row;
            lastRow = row + categoryStats.Count;

            // category list
            range = sheet.get_Range("B" + row, "J" + lastRow);
            Excel.Range catRange = sheet.get_Range("K" + row, "L" + lastRow);
            catRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));


            // formatting
            range.Font.Name = "Verdana";
            sheet.Range["D" + row + ":H" + row].MergeCells = true;



            // headers
            sheet.Range["D" + row].Value2 = "Task Category";
            sheet.Range["I" + row].Value2 = "Hours";
            sheet.Range["J" + row].Value2 = "%";
            sheet.Range["J" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            // hcart headers
            sheet.Range["K" + row].Value2 = "Category";
            sheet.Range["L" + row].Value2 = "Hours";

            // reorder categories
            categoryStats = categoryStats.OrderByDescending(x => x.Hours).ToList();

            // headers
            Excel.Range catHeaderRange = sheet.Range["B" + row + ":J" + row];
            catHeaderRange.Font.Bold = true;
            catHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            catHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(142, 169, 219));


            // eneter records
            ++row;
            foreach (var c in categoryStats)
            {

                // formatting
                Excel.Range r = sheet.Range["B" + row + ":J" + row];
                sheet.Range["D" + row + ":H" + row].MergeCells = true;
                r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(68, 114, 196));
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["I" + row].NumberFormat = "0.00";


                //sheet.Range["B" + row].Value2 = t.Title;
                //sheet.Range["C" + row].Value2 = String.Format("{0}/{1}", t.Category.CategoryName, t.SubCategory.CategoryName);
                sheet.Range["D" + row].Value2 = c.Name;
                sheet.Range["I" + row].Value2 = String.Format("{0:0.00}", c.Hours);
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["J" + row].Value2 = c.Hours / summary.TotalHours;
                sheet.Range["J" + row].NumberFormat = "00%";
                sheet.Range["J" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


                // chart data area
                sheet.Range["K" + row].Value2 = c.Name;
                sheet.Range["L" + row].Value2 = c.Hours;


                ++row;
            }

            // pie chart
            Excel.Chart catChart = chartObjects.Add(700, 725, 500, 300).Chart;

            // format chart
            catChart.SetSourceData(catRange, Type.Missing);
            catChart.ChartType = Excel.XlChartType.xlDoughnut;
            catChart.HasTitle = true;
            catChart.ChartTitle.Text = "Task Category Hours";
            catChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent);

            #endregion
        }


    }

    public class ProjectSummaryReportModel
    {
        public int ProjectCount { get; set; }
        public int TaskCount { get; set; }
        public double TotalHours { get; set; }
        public double AverageTaskTime
        {
            get
            {
                double avg = 0;

                if (this.TotalHours > 0)
                    avg = this.TotalHours / this.TaskCount;

                return avg;
            }
        }
    }

    public class WeeklySummaryReport
    {
        public int ProjectCount { get; set; }
        public int TaskCount { get; set; }
        public double TotalHours { get; set; }
        public double AverageTaskTime
        {
            get
            {
                double avg = 0;

                if (this.TotalHours > 0)
                    avg = this.TotalHours / this.TaskCount;

                return avg;
            }
        }
        public List<ProjectReportModel> Projects { get; set; }
        public List<TaskReportModel> Tasks { get; set; }
    }

    public struct CategoryHours
    {
        public string Name { get; set; }
        public double Hours { get; set; }
    }

    public class ProjectReportModel
    {
        #region Properties...
        public Guid ProjectGuid { get; set; }
        public Guid ProjectCategoryGuid { get; set; }
        public Guid PriorityGuid { get; set; }
        public string ProjectName { get; set; }
        public string Description { get; set; }
        public double EstimatedHours { get; set; }
        public DateTime StartedDate { get; set; }
        public DateTime DueDate { get; set; }
        public DateTime CompletedDate { get; set; }
        public DateTime CreatedDate { get; set; }
        public StatusModel Status { get; set; }
        public double ProjectHours { get; set; }
        public int TaskCount { get; set; }
        public double AverageTaskTime
        {
            get
            {
                double avg = 0;

                if (this.ProjectHours > 0)
                    avg = this.ProjectHours / this.TaskCount;

                return avg;
            }
        }
        public CategoryModel Category { get; set; }
        public List<TaskReportModel> Tasks { get; set; }
        #endregion
    }

    public class TaskReportModel
    {
        public Guid TaskGuid { get; set; }
        public Guid ProjectGuid { get; set; }
        public Guid BranchGuid { get; set; }
        public Guid CategoryGuid { get; set; }
        public Guid SubCategoryGuid { get; set; }
        public Guid PriorityGuid { get; set; }
        public bool IsProjecTask { get; set; }
        public bool IsBranchTask { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public double EstimatedHours { get; set; }
        public DateTime StartedDate { get; set; }
        public DateTime DueDate { get; set; }
        public DateTime CompletedDate { get; set; }
        public double TaskHours { get; set; }
        public CategoryModel Category { get; set; }
        public CategoryModel SubCategory { get; set; }
        public StatusModel Status { get; set; }
    }
}
